use SAD_Assignment1
GO
IF OBJECT_ID (N'getPhotoList', N'IF') IS NOT NULL
    DROP FUNCTION getPhotoList;
GO
CREATE FUNCTION getPhotoList (@page int, @maxPhoto int, @photoType int)
RETURNS TABLE
AS
RETURN
(
	WITH subResult AS
	(
	SELECT  p.ID, p.img_url, p.title, p.post_date, p.acc_ID, a.name,		
		ROW_NUMBER() OVER (ORDER BY p.post_date DESC) AS rowNum
	FROM Photo AS p
	JOIN Account AS a ON p.acc_ID = a.ID
	WHERE p.img_type = @photoType
	)
	SELECT *
	FROM subResult
	WHERE rowNum BETWEEN (@page-1)*@maxPhoto+1 AND @page*@maxPhoto	
);
GO
select * from Photo order by post_date desc
select * from getPhotoList(2, 3, 1)
GO
----------------------------------------------------------------------------------
----------------------------------------------------------------------------------
IF OBJECT_ID (N'getPhoto', N'IF') IS NOT NULL
    DROP FUNCTION getPhoto;
GO
CREATE FUNCTION getPhoto (@photoID int)
RETURNS TABLE
AS
RETURN
(	
	SELECT  p.ID, p.img_url, p.title, p.post_date, p.acc_ID, a.name, pt.[type_name]
	FROM Photo AS p
	JOIN Account AS a ON p.acc_ID = a.ID
	JOIN PhotoTypes AS pt ON pt.ID = p.img_type
	WHERE p.ID = @photoID
);
GO
select * from getPhoto(1)
select * from Photo
GO
----------------------------------------------------------------------------------
----------------------------------------------------------------------------------
IF OBJECT_ID (N'getComment', N'IF') IS NOT NULL
    DROP FUNCTION getComment;
GO
CREATE FUNCTION getComment(@photoID int)
RETURNS TABLE
AS
RETURN
(	
	SELECT c.ID, c.acc_ID,c.post_date,c.body, a.name
	FROM Comments c	
	JOIN Account a ON a.ID = c.acc_ID
	WHERE c.photo_ID = @photoID	
);
GO
select * from getComment(2)
GO
----------------------------------------------------------------------------------
----------------------------------------------------------------------------------
IF OBJECT_ID (N'getReply', N'IF') IS NOT NULL
    DROP FUNCTION getReply;
GO
CREATE FUNCTION getReply(@cmtID int)
RETURNS TABLE
AS
RETURN
(	
	SELECT cr.ID, cr.acc_ID,cr.post_date,cr.body, a.name
	FROM CommentReplies cr
	JOIN Account a ON a.ID = cr.acc_ID
	WHERE cr.cmt_ID = @cmtID
);
GO
select * from getReply(2)
select * from CommentReplies	
GO
----------------------------------------------------------------------------------
----------------------------------------------------------------------------------
IF OBJECT_ID (N'countLikePhoto', N'FN') IS NOT NULL
    DROP FUNCTION countLikePhoto;
GO
CREATE FUNCTION countLikePhoto(@photoID int, @isLike bit)
RETURNS INT
AS
BEGIN
	DECLARE @numOfLike INT
	SELECT @numOfLike = COUNT(*)
	FROM PhotoLikes
	WHERE photo_ID=@photoID AND liked = @isLike
	RETURN @numOfLike
END
GO
select dbo.countLikePhoto(1,0)
select * from PhotoLikes
GO
----------------------------------------------------------------------------------
----------------------------------------------------------------------------------
IF OBJECT_ID (N'checkLogin', N'FN') IS NOT NULL
    DROP FUNCTION checkLogin;
GO
CREATE FUNCTION checkLogin(@email varchar(100), @pass varchar(100))
RETURNS BIT
AS
BEGIN
	DECLARE @row INT	
	SELECT @row = COUNT(*)
	FROM Account
	WHERE email = @email AND [password] = @pass
	IF (@row > 0) RETURN 1
	RETURN 0
END
GO
select * from Account
select dbo.checkLogin('admin','123456')
GO
----------------------------------------------------------------------------------
----------------------------------------------------------------------------------
IF OBJECT_ID (N'addNewAccount', N'P') IS NOT NULL
    DROP PROCEDURE addNewAccount;
GO
CREATE PROCEDURE addNewAccount(@ID int OUT, @email varchar(100), @pass varchar(100), 
	@name nvarchar(100), @roles nvarchar(50))
AS
BEGIN
	DECLARE @row INT, @role_ID INT
	SELECT @row = COUNT(*)
	FROM Account
	WHERE email = @email
	IF (@row > 0)
		SET @ID = 0
	ELSE 
	BEGIN		
		SELECT @role_ID=ID FROM AccountRoles WHERE role_name=@roles
		INSERT INTO Account(email,[password],name,role_ID)
		VALUES(@email,@pass,@name,@role_ID);
		SELECT @ID = ID FROM Account WHERE email = @email		
	END
END
GO
DECLARE @ID int
exec addNewAccount @ID OUT,'customer2','123456','customer 2','deactive'
select @ID
select * from Account
GO
----------------------------------------------------------------------------------
----------------------------------------------------------------------------------
IF OBJECT_ID (N'isAccountExist', N'FN') IS NOT NULL
    DROP FUNCTION isAccountExist;
GO
CREATE FUNCTION isAccountExist(@email varchar(100))
RETURNS BIT
AS
BEGIN
	DECLARE @row INT	
	SELECT @row = COUNT(*)
	FROM Account
	WHERE email = @email
	IF (@row > 0) RETURN 1
	RETURN 0
END
GO
select dbo.isAccountExist('admin2')
select * from Account
GO
----------------------------------------------------------------------------------
----------------------------------------------------------------------------------
IF OBJECT_ID (N'getAccountRole', N'FN') IS NOT NULL
    DROP FUNCTION getAccountRole;
GO
CREATE FUNCTION getAccountRole(@email varchar(100))
RETURNS nvarchar(50)
AS
BEGIN
	DECLARE @role nvarchar(50)	
	SELECT @role = ar.role_name
	FROM Account a
	JOIN AccountRoles ar ON ar.ID = a.role_ID
	WHERE email = @email	
	RETURN @role
END
GO
select dbo.getAccountRole('customer1')
select * from Account
GO
----------------------------------------------------------------------------------
----------------------------------------------------------------------------------
IF OBJECT_ID (N'activeAccount', N'P') IS NOT NULL
    DROP PROCEDURE activeAccount;
GO
CREATE PROCEDURE activeAccount(@email varchar(100), @ID int, @result bit out)
AS
BEGIN	
	SELECT @result = COUNT(*)
	FROM Account a	
	WHERE email = @email AND ID = @ID
	IF @result > 0
	BEGIN
		UPDATE Account SET role_ID = 3 WHERE ID = @ID		
	END	
END
GO
select * from Account
declare @result bit
exec activeAccount 'customer2@email.com', 6, @result out
select @result
GO
----------------------------------------------------------------------------------
----------------------------------------------------------------------------------
IF OBJECT_ID (N'addNewPhoto', N'P') IS NOT NULL
    DROP PROCEDURE addNewPhoto;
GO
CREATE PROCEDURE addNewPhoto(@title nvarchar(250), @img_url varchar(250), @email varchar(100), @result bit out)
AS
BEGIN		
	DECLARE @ID INT
	SET @ID = 0
	SELECT @ID = ID
	FROM Account
	WHERE email=@email
	IF @ID = 0 --The Account does not exist
	BEGIN
		SET @result = 0
		RETURN 
	END
	SET @result = 1
	INSERT INTO Photo(title,img_url,acc_ID,post_date,img_type)
	VALUES(@title,@img_url,@ID,GETDATE(),2)	
END
GO
declare @result bit
exec addNewPhoto 'title','url','customer2@email.com2', @result out
select @result
select * from Photo
GO
----------------------------------------------------------------------------------
----------------------------------------------------------------------------------
IF OBJECT_ID (N'addComment', N'P') IS NOT NULL
    DROP PROCEDURE addComment;
GO
CREATE PROCEDURE addComment(@photoID INT, @body NTEXT, @email varchar(100), @result bit out)
AS
BEGIN		
	DECLARE @ID INT
	SET @ID = 0
	SELECT @ID = ID
	FROM Account
	WHERE email=@email
	IF @ID = 0 --The Account does not exist
	BEGIN
		SET @result = 0
		RETURN 
	END
	SET @result = 1
	INSERT INTO Comments(acc_ID,post_date,body,photo_ID)
	VALUES(@ID,GETDATE(),@body,@photoID)	
END
GO
select * from Comments
declare @result bit
exec addComment 2,'comment by sql','customer1@email.com',@result
GO
----------------------------------------------------------------------------------
----------------------------------------------------------------------------------
IF OBJECT_ID (N'getAccountInfo', N'IF') IS NOT NULL
    DROP FUNCTION getAccountInfo;
GO
CREATE FUNCTION getAccountInfo(@email varchar(100))
RETURNS TABLE
AS
RETURN
(	
	SELECT a.ID,a.name,a.[password],ar.role_name
	FROM Account a
	JOIN AccountRoles ar ON ar.ID = a.role_ID
	WHERE email = @email		
	);
GO
select * from getAccountInfo('customer1@email.com')
GO
----------------------------------------------------------------------------------
----------------------------------------------------------------------------------
IF OBJECT_ID (N'deleteComment', N'P') IS NOT NULL
    DROP PROCEDURE deleteComment;
GO
CREATE PROCEDURE deleteComment(@cmtID INT, @result bit out)
AS
BEGIN		
	DECLARE @row INT	
	SELECT @row = COUNT(*)
	FROM Comments
	WHERE ID = @cmtID
	IF @row = 0 --The Comment does not exist
	BEGIN
		SET @result = 0
		RETURN 
	END
	SET @result = 1
	
	DELETE FROM CommentReplies WHERE cmt_ID = @cmtID
	DELETE FROM Comments WHERE ID = @cmtID
END
GO
select * from Comments
declare @result bit
exec deleteComment 10,@result
GO
----------------------------------------------------------------------------------
----------------------------------------------------------------------------------
IF OBJECT_ID (N'addReply', N'P') IS NOT NULL
    DROP PROCEDURE addReply;
GO
CREATE PROCEDURE addReply(@cmtID INT, @body NTEXT, @email varchar(100), @result bit out)
AS
BEGIN		
	DECLARE @ID INT
	SET @ID = 0
	SELECT @ID = ID
	FROM Account
	WHERE email=@email
	IF @ID = 0 --The Account does not exist
	BEGIN
		SET @result = 0
		RETURN 
	END
	SET @result = 1
	INSERT INTO CommentReplies(acc_ID,post_date,body,cmt_ID)
	VALUES(@ID,GETDATE(),@body,@cmtID)	
END
GO
select * from CommentReplies
declare @result bit
exec addReply 2,'comment by sql 2','customer1@email.com',@result
GO
----------------------------------------------------------------------------------
----------------------------------------------------------------------------------
IF OBJECT_ID (N'deleteReply', N'P') IS NOT NULL
    DROP PROCEDURE deleteReply;
GO
CREATE PROCEDURE deleteReply(@replyID INT, @result bit out)
AS
BEGIN		
	DECLARE @row INT	
	SELECT @row = COUNT(*)
	FROM CommentReplies
	WHERE ID = @replyID
	IF @row = 0 --The Comment does not exist
	BEGIN
		SET @result = 0
		RETURN 
	END
	SET @result = 1
	
	DELETE FROM CommentReplies WHERE ID = @replyID
END
GO
select * from CommentReplies
declare @result bit
exec deleteReply 6,@result out
select @result
GO
----------------------------------------------------------------------------------
----------------------------------------------------------------------------------
IF OBJECT_ID (N'LikePhoto', N'P') IS NOT NULL
    DROP PROCEDURE LikePhoto;
GO
CREATE PROCEDURE LikePhoto(@photoID INT, @accID varchar(100), @isLike bit, @result bit out)
AS
BEGIN			
	DECLARE @row INT
	SELECT @row = COUNT(*) FROM Photo WHERE ID=@photoID
	IF (@row = 0) --Photo does not exist
	BEGIN
		SET @result = 0
		RETURN
	END
	SET @row = 0
	SELECT @row = COUNT(*) FROM Account WHERE ID = @accID
	IF @row = 0 --Account does not exist
	BEGIN 
		SET @result = 0 
		RETURN 
	END
	SET @row = 0	
	SELECT @row = COUNT(*)FROM PhotoLikes WHERE photo_ID=@photoID AND @accID = acc_ID
	IF @row = 0 --The PhotoLike does not exist
	BEGIN
		INSERT INTO PhotoLikes(photo_ID,acc_ID,liked)
		VALUES(@photoID,@accID,@isLike)
	END ELSE --The PhotoLike exists
	BEGIN
		UPDATE PhotoLikes
		SET liked = @isLike
		WHERE photo_ID=@photoID AND acc_ID=@accID
	END
	SET @result = 1
		
END
GO
declare @result bit
exec LikePhoto 224,1,1,@result out
select @result
select * from PhotoLikes
GO
----------------------------------------------------------------------------------
----------------------------------------------------------------------------------
IF OBJECT_ID (N'IsLikePhoto', N'FN') IS NOT NULL
    DROP FUNCTION IsLikePhoto;
GO
CREATE FUNCTION IsLikePhoto(@photoID INT, @accID INT, @isLike bit)
RETURNS BIT AS
BEGIN			
	DECLARE @row INT		
	SELECT @row = COUNT(*)FROM PhotoLikes 
	WHERE photo_ID=@photoID AND @accID = acc_ID AND liked = @isLike
	IF @row = 1 RETURN 1	
	RETURN 0				
END
GO
select dbo.IsLikePhoto(2,3,1)
select * from PhotoLikes where photo_ID=2
select * from Account where ID=1
GO
----------------------------------------------------------------------------------
----------------------------------------------------------------------------------
IF OBJECT_ID (N'photoCount', N'FN') IS NOT NULL
    DROP FUNCTION photoCount;
GO
CREATE FUNCTION photoCount(@accID INT)
RETURNS INT AS
BEGIN			
	DECLARE @row INT		
	SELECT @row = COUNT(*)FROM Photo 
	WHERE acc_ID = @accID		
	RETURN @row				
END
GO
select dbo.photoCount(16)
select * from Photo where acc_ID=6
GO
----------------------------------------------------------------------------------
----------------------------------------------------------------------------------
IF OBJECT_ID (N'photoLikeCount', N'FN') IS NOT NULL
    DROP FUNCTION photoLikeCount;
GO
CREATE FUNCTION photoLikeCount(@accID INT, @isLike BIT)
RETURNS INT AS
BEGIN			
	DECLARE @row INT		
	SELECT @row = COUNT(*)FROM PhotoLikes 
	WHERE acc_ID = @accID AND liked = @isLike		
	RETURN @row				
END
GO
select dbo.photoLikeCount(61,0)
select * from PhotoLikes where acc_ID=6
GO
----------------------------------------------------------------------------------
----------------------------------------------------------------------------------
IF OBJECT_ID (N'getUserInfo', N'IF') IS NOT NULL
    DROP FUNCTION getUserInfo;
GO
CREATE FUNCTION getUserInfo(@accID INT)
RETURNS TABLE
AS
RETURN
(	
	SELECT a.ID,a.email,a.name,ar.role_name, dbo.photoCount(a.ID) AS 'photoCount',
		dbo.photoLikeCount(@accID,1) AS 'photoLikeCount',
		dbo.photoLikeCount(@accID,0) AS 'photoUnlikeCount'
	FROM Account a
	JOIN AccountRoles ar ON ar.ID = a.role_ID
	WHERE a.ID = @accID		
	);
GO
select * from getUserInfo(6)
select * from PhotoLikes where photo_ID IN (select ID from Photo where acc_ID=6)
GO
----------------------------------------------------------------------------------
----------------------------------------------------------------------------------
IF OBJECT_ID (N'ApprovePhoto', N'P') IS NOT NULL
    DROP PROCEDURE ApprovePhoto;
GO
CREATE PROCEDURE ApprovePhoto(@photoID INT, @result bit out)
AS
BEGIN			
	DECLARE @row INT
	SELECT @row = COUNT(*) FROM Photo WHERE ID=@photoID
	IF (@row = 0) --Photo does not exist
	BEGIN
		SET @result = 0
		RETURN
	END
	UPDATE Photo SET img_type=1,post_date=GETDATE() WHERE ID=@photoID
	SET @result = 1
		
END
GO
declare @result bit
exec ApprovePhoto 7,@result out
select * from Photo
GO
----------------------------------------------------------------------------------
----------------------------------------------------------------------------------
IF OBJECT_ID (N'DeletePhoto', N'P') IS NOT NULL
    DROP PROCEDURE DeletePhoto;
GO
CREATE PROCEDURE DeletePhoto(@photoID INT, @result bit out)
AS
BEGIN			
	DECLARE @row INT
	SELECT @row = COUNT(*) FROM Photo WHERE ID=@photoID
	IF (@row = 0) --Photo does not exist
	BEGIN
		SET @result = 0
		RETURN
	END
	DELETE FROM Photo WHERE ID = @photoID
	SET @result = 1
		
END
GO
declare @result bit
exec DeletePhoto 47,@result out
select * from Photo where ID=47
select * from PhotoLikes where photo_ID=47
select * from Comments where photo_ID=47
select * from CommentReplies where cmt_ID IN (select ID from Comments where photo_ID=47)
select * from Account
GO
----------------------------------------------------------------------------------
----------------------------------------------------------------------------------
IF OBJECT_ID (N'UpdateAccount', N'P') IS NOT NULL
    DROP PROCEDURE UpdateAccount;
GO
CREATE PROCEDURE UpdateAccount(@accID INT, @email VARCHAR(100), @pass VARCHAR(100),@name NVARCHAR(100),
	@roleName NVARCHAR(50), @result bit out)
AS
BEGIN			
	DECLARE @row INT
	SELECT @row = COUNT(*) FROM Account WHERE ID=@accID
	IF (@row = 0) --Account does not exist
	BEGIN
		SET @result = 0
		RETURN
	END
	SELECT @row=ID FROM AccountRoles WHERE role_name=@roleName --get roles ID
	UPDATE Account SET email=@email,name=@name,[password] = @pass, role_ID=@row
	WHERE ID=@accID
	SET @result = 1
		
END
GO
declare @result bit
exec UpdateAccount 4,'member2','123456','thanh vien 2','member',@result out
select * from Account
----------------------------------------------------------------------------------
----------------------------------------------------------------------------------
IF OBJECT_ID (N'getAccountList', N'IF') IS NOT NULL
    DROP FUNCTION getAccountList;
GO
CREATE FUNCTION getAccountList()
RETURNS TABLE
AS
RETURN
(	
	SELECT a.ID,a.name,a.[password],ar.role_name, a.email
	FROM Account a
	JOIN AccountRoles ar ON ar.ID = a.role_ID	
	);
GO
select * from getAccountList()
GO
----------------------------------------------------------------------------------
----------------------------------------------------------------------------------
IF OBJECT_ID (N'getAccountByID', N'IF') IS NOT NULL
    DROP FUNCTION getAccountByID;
GO
CREATE FUNCTION getAccountByID(@accID INT)
RETURNS TABLE
AS
RETURN
(	
	SELECT a.ID,a.name,a.[password],ar.role_name, a.email
	FROM Account a
	JOIN AccountRoles ar ON ar.ID = a.role_ID	
	WHERE a.ID = @accID
	);
GO
select * from getAccountByID(1)
GO
select * from AccountRoles
